﻿using Dapper;
using System.Data.SqlClient;
using System.Text;
using yunbo.Model.BI;
using yunbo.Model.BIQI;
using yunbo.Model.SSM;

namespace yunbo.Repositorys.BIRepositorys
{
	public class DictRepository
	{
		public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User Id=sa;Password=geili@2025;TrustServerCertificate=true;";

		/// <summary>
		/// 获取所有字典类型
		/// </summary>
		/// <returns></returns>
		public List<Dict_type> GetDict_types()
		{
			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = $"select * from [dbo].[dict_type]";
				List<Dict_type> dict_types = connection.Query<Dict_type>(sql).ToList();
				return dict_types;
			}
		}


		/// <summary>
		/// 根据str查询字典类型
		/// </summary>
		/// <param name="str"></param>
		/// <returns></returns>
		public List<Dict_type> GetDict_type(string str)
		{
			using (SqlConnection connection = new SqlConnection(data))
			{
				// 使用参数化查询
				string sql = "SELECT dict_type_id, dict_type_name FROM [dbo].[dict_type] WHERE dict_type_name LIKE @searchTerm";

				// 添加参数，注意在参数值中添加百分号以支持模糊匹配
				var parameters = new { searchTerm = $"%{str}%" };

				// 使用 Dapper 执行查询
				List<Dict_type> dict_type = connection.Query<Dict_type>(sql, parameters).ToList();

				return dict_type;
			}
		}


		/// <summary>
		/// 根据字典类型的id获取一种字典
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public List<Dict> GetOneDict(int id)
		{

			List<Dict> dict = new List<Dict>();

			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = @"select [dictionary_id],dictionary_code,[dictionary_name] from [dbo].[dictionary] where [dictionary_type]=@id";

				connection.Open();

				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					command.Parameters.AddWithValue("@id", id);
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							dict.Add(new Dict
							{
								dictionary_id = Convert.ToInt32(reader["dictionary_id"]),
								dictionary_code = reader["dictionary_code"].ToString(),
								dictionary_name = reader["dictionary_name"].ToString()
							});
						}
					}
				}
			}
			return dict;
		}


		/// <summary>
		/// 查询字典
		/// </summary>
		/// <param name="type"></param>
		/// <param name="state"></param>
		/// <param name="str"></param>
		/// <returns></returns>
		public Dict_FY GetDict(int page, int pageSize, int type, int state, string? str)
		{
			var result = new Dict_FY();
			var parameters = new DynamicParameters();

			// 公共参数
			parameters.Add("@state", state);
			parameters.Add("@page", page);
			parameters.Add("@pageSize", pageSize);

			// 构建基础查询条件
			var whereClause = new StringBuilder(@" 
        WHERE a.dictionary_isdelete = 1 
        AND a.dictionary_state = @state");

			if (type != 0)
			{
				whereClause.Append(" AND a.dictionary_type = @dictTypeId");
				parameters.Add("@dictTypeId", type);
			}

			if (!string.IsNullOrWhiteSpace(str))
			{
				whereClause.Append(" AND (a.dictionary_code LIKE @str OR a.dictionary_name LIKE @str)");
				parameters.Add("@str", $"%{str}%");
			}

			using (var connection = new SqlConnection(data))
			{
				connection.Open();

				// 主查询（分页数据）
				var mainSql = $@"
            SELECT 
                a.dictionary_id,
                a.dictionary_code,
                a.dictionary_name,
                a.dictionary_type,
                b.dict_type_name,
                a.dictionary_state,
                a.dictionary_notes
            FROM [dbo].[dictionary] a
            JOIN [dbo].[dict_type] b ON a.dictionary_type = b.dict_type_id
            {whereClause}
            ORDER BY a.dictionary_id
            OFFSET (@page - 1) * @pageSize ROWS 
            FETCH NEXT @pageSize ROWS ONLY";

				result.dictionaries = connection.Query<Dictionary>(mainSql, parameters).ToList();

				// 总数查询（优化后的独立查询）
				var countSql = new StringBuilder(@"
            SELECT COUNT(1) 
            FROM [dbo].[dictionary] a
            WHERE a.dictionary_isdelete = 1 
            AND a.dictionary_state = @state");

				if (type != 0) countSql.Append(" AND a.dictionary_type = @dictTypeId");
				if (!string.IsNullOrWhiteSpace(str)) countSql.Append(" AND (a.dictionary_code LIKE @str OR a.dictionary_name LIKE @str)");

				result.zon = connection.ExecuteScalar<int>(countSql.ToString(), parameters);
			}

			return result;
		}



		/// <summary>
		/// 字典列表弹窗
		/// </summary>
		/// <param name="codes"></param>
		/// <returns></returns>
		public List<Dictionary> GetDict_Codes(int type,string? codes)
		{
			List<Dictionary> dictionaries = new List<Dictionary>();

			// 处理空或null的输入
			if (string.IsNullOrEmpty(codes))
			{
				return dictionaries;
			}

			// 清理和分割codes，移除空项和空格
			var codeList = codes.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
								.Select(s => s.Trim())
								.Where(s => !string.IsNullOrEmpty(s))
								.ToList();

			// 如果没有有效的code，直接返回空列表
			if (codeList.Count == 0)
			{
				return dictionaries;
			}

			// 判断是否需要查询全部记录（codes='0'）
			bool shouldQueryAll = codeList.Count == 1 && codeList[0] == "0";

			using (SqlConnection connection = new SqlConnection(data))
			{
				try
				{
					// 基础SQL查询
					string sql = $"select dictionary_id,[dictionary_code],[dictionary_name] from [dbo].[dictionary] where [dictionary_type]={type} and [dictionary_state]=1  AND [dictionary_isdelete]=1";

					// 如果不需要查询全部记录，添加IN子句
					if (!shouldQueryAll)
					{
						sql += $" AND [dictionary_code] IN (SELECT value FROM STRING_SPLIT(@codes, ',') WHERE value IS NOT NULL)";
					}

					connection.Open();

					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						// 如果不需要查询全部记录，添加参数
						if (!shouldQueryAll)
						{
							command.Parameters.AddWithValue("@codes", string.Join(",", codeList));
						}

						using (SqlDataReader reader = command.ExecuteReader())
						{
							while (reader.Read())
							{
								dictionaries.Add(new Dictionary
								{
									dictionary_id = Convert.ToInt32(reader["dictionary_id"]),
									dictionary_code = reader["dictionary_code"].ToString(),
									dictionary_name = reader["dictionary_name"].ToString(),
								});
							}
						}
					}
				}
				catch (Exception ex)
				{
					// 记录日志或抛出异常
					throw new ApplicationException("An error occurred while fetching data from the database.", ex);
				}
				finally
				{
					connection.Close();
				}
			}

			return dictionaries;
		}

		/// <summary>
		/// 状态改变
		/// </summary>
		/// <param name="id"></param>
		/// <param name="state"></param>
		/// <returns></returns>
		public bool StateChange(int id, int state)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"update [dbo].[dictionary] set [dictionary_state]=@State where [dictionary_id]=@Id";
					return connection.Execute(sql, new { Id = id, State = state }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"状态更改失败: {ex.Message}");
				return false;
			}
		}


		/// <summary>
		/// 新增字典
		/// </summary>
		/// <param name="dict"></param>
		/// <returns></returns>
		public bool AddDict(Dictionary dict)
		{
			string sql = @"
            INSERT INTO [dbo].[dictionary] 
            (dictionary_code, dictionary_name, dictionary_type, dictionary_state, dictionary_notes, dictionary_create_by) 
            VALUES 
            (@code, @name, @type, 1, @notes, 1)";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@code", dict.dictionary_code);
						command.Parameters.AddWithValue("@name", dict.dictionary_name);
						command.Parameters.AddWithValue("@type", dict.dictionary_type);
						command.Parameters.AddWithValue("@notes", dict.dictionary_notes);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}


		/// <summary>
		/// 修改字典
		/// </summary>
		/// <param name="dict"></param>
		/// <returns></returns>
		public bool UpdateDict(Dictionary dict)
		{
			string sql = @"update [dbo].[dictionary] set dictionary_name=@name, dictionary_type=@type,
			dictionary_notes=@notes, dictionary_update_by=1, dictionary_update_time=GETDATE() where [dictionary_id]=@id";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@id", dict.dictionary_id);
						command.Parameters.AddWithValue("@name", dict.dictionary_name);
						command.Parameters.AddWithValue("@type", dict.dictionary_type);
						command.Parameters.AddWithValue("@notes", dict.dictionary_notes);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}


		/// <summary>
		/// 删除字典
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public bool DelDict(int id)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"UPDATE [dbo].[dictionary] 
                       SET dictionary_isdelete = 0 
                       WHERE dictionary_id = @Id";

					return connection.Execute(sql, new { Id = id }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"删除失败: {ex.Message}");
				return false;
			}
		}


		/// <summary>
		/// 批量删除字典
		/// </summary>
		/// <param name="ids"></param>
		/// <returns></returns>
		public bool DelDicts(int[] ids)
		{
			// 参数验证
			if (ids == null || ids.Length == 0)
				return false;

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (var transaction = connection.BeginTransaction()) // 添加事务
					{
						try
						{
							string sql = @"UPDATE [dbo].[dictionary] 
                                   SET dictionary_isdelete = 0 
                                   WHERE dictionary_id IN @Ids";

							int affectedRows = connection.Execute(sql, new { Ids = ids }, transaction);
							transaction.Commit(); // 提交事务

							// 确保所有ID都成功更新
							return affectedRows == ids.Length;
						}
						catch
						{
							transaction.Rollback(); // 回滚事务
							throw;
						}
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"批量删除失败: {ex.Message}");
				return false;
			}
		}

		public string GetName(int dictionary_id)
		{
			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = $"select [dictionary_name] from [dbo].[dictionary] where [dictionary_id]={dictionary_id}";
				string name = connection.ExecuteScalar<string>(sql); // 使用 ToList() 获取所有菜单  
				return name;
			}
		}
	}

}
